select ro.id id, ro.flat_details_id flatDetailsId,
        ro.external_id externalId, fd.kitchen_square kitchenSquare, fd.max_level maxLevel,
        fd.tel_numbers telNumbers, fd.living_square livingSquare, fd.common_square commonSquare,
        fd.level level, fd.address address, fd.balcony_num balconyNum, fd.adv_info advInfo, fd.rooms rooms,
        p.state state, p.date date, p.price_value priceValue,
         (select if(p.price_value=sp.price_value,0,if(p.price_value>sp.price_value,1,-1))
                 from price sp
                 where
                     sp.realty_object_id = ro.id
                     and
                      sp.date < p.date
             order by sp.date desc limit 1
         ) dynamic

from realty_object ro
    join flat_details fd on fd.id = ro.flat_details_id
    join price p on p.realty_object_id = ro.id
    where p.date = (select max(sp.date) from price sp where sp.realty_object_id = ro.id)
    and p.state in (0,1)
order by p.state desc, ro.external_id asc